﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace XMK.Utils
{
    public class ExcelHelper
    {
        /// <summary>
        /// 此连接只能操作Excel2007之前(.xls)文件
        /// </summary>
        //private static string excelConn = "Provider=Microsoft.Jet.OleDb.4.0;data source= {0};Extended Properties='Excel 8.0; HDR=NO; IMEX=1'";
        /// <summary>
        /// 此连接可以操作.xls与.xlsx文件
        /// </summary>
        private static string excelConn = "Provider=Microsoft.Ace.OleDb.12.0; data source={0};Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";

        //出现未在本地计算机上注册“Microsoft.Ace.OleDb.12.0”提供程序。
        //就去下载这个:AccessDatabaseEngine.exe
        //下载地址:http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe 

        /// <summary>
        /// 根据Excel物理路径、表名(Sheet名)获取数据集
        /// </summary>
        /// <param name="fileFullPath"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable GetExcelToDataTableBySheet(string fileFullPath, string sheetName)
        {
            string strConn = string.Format(excelConn, fileFullPath);
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", sheetName), conn);//("select * from [Sheet1$]", conn);
            odda.Fill(ds, sheetName);
            conn.Close();
            return ds.Tables[0];
        }

        /// <summary>
        /// 将第一个sheet转换成datatable
        /// </summary>
        /// <param name="fileFullPath"></param>
        /// <returns></returns>
        public static DataTable ConvertFirstSheetToDataTable(string fileFullPath)
        {
            string strConn = string.Format(excelConn, fileFullPath); 
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
                return null;
            string firstSheetName = ParseHelper.Parse<string>(dt.Rows[0]["TABLE_NAME"]);
            DataSet ds = new DataSet();
            OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", firstSheetName), conn);//("select * from [Sheet1$]", conn);
            odda.Fill(ds, firstSheetName);
            conn.Close();
            dt.Dispose();
            return ds.Tables[0];
        }

        /// <summary>
        /// 取得excel下的所有sheet名
        /// </summary>
        /// <param name="excelFile"></param>
        /// <returns></returns>
        public static String[] GetExcelSheetNames(string excelFile)
        {
            OleDbConnection objConn = null;
            System.Data.DataTable dt = null;
            string strConn = string.Format(excelConn, excelFile); 
            objConn = new OleDbConnection(strConn);
            objConn.Open();
            dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dt == null)
            {
                return null;
            }
            String[] excelSheets = new String[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                excelSheets[i] = row["TABLE_NAME"].ToString();
                i++;
            }
            if (objConn != null)
            {
                objConn.Close();
                objConn.Dispose();
            }
            if (dt != null)
            {
                dt.Dispose();
            }
            return excelSheets;
        }

    }
}
